
<#
.SYNOPSIS
Create a database connection Object from a XML source

.DESCRIPTION
Transfrom a xml element to a database Object.
The XML Element must have a well defined provider value according the .Net specifications
and must have also a valid connectionString

.PARAMETER source
Required. the xml element which contains the database connection defintion
#>
Function New-DatabaseConnection
{
        Param( 
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)] 
        [System.Xml.XmlElement] 
        $source
        )

    Begin
    {
        $watch = [system.diagnostics.stopwatch]::StartNew() 
        write-verbose "Start New-DatabaseConnection from $($source.LocalName)"
    }
    End
    {
        write-verbose "End New-DatabaseConnection from $($source.LocalName)"
        $watch.Stop();
        Write-Verbose "New-DatabaseConnection duration : $($watch.Elapsed)"
    }
    Process 
    {

        #Add mandatory properties
        $newObj = New-Object System.Object       

        #Add Dynamic properties
        $source.Attributes | ForEach-Object{
            
                Write-Verbose "Add dynamic property $($_.Name) from component tag with Value $($_.Value)"
                $newObj | Add-Member -type NoteProperty -name $_.Name -value $_.Value -ErrorAction SilentlyContinue
            
        }
       
        #Add script property to obtain the database connection
        $newObj | Add-Member -MemberType ScriptProperty -Name DbFactory -Value {

            $f = [System.Data.Common.DbProviderFactories]::GetFactory($this.provider)
            Write-Output $f           

        }

        #TODO : Add check connection definition

        Write-Output $newObj
    }
}


1<#
.SYNOPSIS
Execute a ExecuteNonQuery stored procedure

.DESCRIPTION
Use this cmdlet to execute read/write stored procedure.
You can use it inside a already opened transaction

.PARAMETER DatabaseConnection
Required. The powershell database connection object (Not DbConnection of the framework.Net). The DBConnection si provided by this Db Factory property of this object
.PARAMETER StoredProcedure
Required. The stored procedure name
.PARAMETER InputObject
Required. A well formed object with properties named as the stored procedure's arguments name and their values
.PARAMETER Cnx
Required. An existing .net DBconnection already opened or not. Must be opend with an existing transaction
.PARAMETER trans
Required. An existing .net DBTransaction
#>
function Execute-DatabaseStoredProc {
    [cmdletbinding(DefaultParameterSetName="none")]
    param (
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
        [PSOBject]
        $DatabaseConnection,

        [Parameter(Mandatory=$true,ValueFromPipeline=$false)]
        [string]$StoredProcedure,

        [Parameter(Mandatory=$true,ValueFromPipeline=$false)]
        [PSObject]$InputObject,

        [Parameter(ParameterSetName="ConnectionExists",Mandatory=$true,ValueFromPipeline=$true)] 
        [Parameter(ParameterSetName="TransactionExists",Mandatory=$true,ValueFromPipeline=$true)] 
        [System.Data.Common.DbConnection]$cnx,

        [Parameter(ParameterSetName="TransactionExists",Mandatory=$true,ValueFromPipeline=$true)] 
        [System.Data.Common.DbTransaction]$trans

    )
    Begin
    {
        $watch = [system.diagnostics.stopwatch]::StartNew() 
        write-verbose "Start Execute-DatabaseStoredProc from $($DatabaseConnection.connectionString) with StoredProc $StoredProcedure"

    }
    End
    {
        write-verbose "End Execute-DatabaseStoredProc from $($DatabaseConnection.connectionString) with StoredProc $StoredProcedure"
        $watch.Stop();
        Write-Verbose "Function duration : $($watch.Elapsed)"
    }
    Process
    {
        #Prepare Database Access
        if (($PSCmdlet.ParameterSetName -ne "ConnectionExists") -and ($PSCmdlet.ParameterSetName -ne "TransactionExists"))
        {
            $cnx = $DatabaseConnection.DbFactory.CreateConnection()
            $cnx.ConnectionString = $DatabaseConnection.connectionString
        }

        $cmd = $DatabaseConnection.DbFactory.CreateCommand()
        $cmd.CommandText = $StoredProcedure
        $cmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $cmd.Connection = $cnx
        $cmd.BindByName = $true

        #Load Params Data
        $InputObject | Get-Member -MemberType NoteProperty | ForEach-Object {
        
            $param = $DatabaseConnection.dbFactory.CreateParameter()
            $param.ParameterName = $_.Name
            $param.Value = $InputObject.$($_.Name)            

            Write-Verbose "Add parameter $($_.Name) with  value $($param.Value)"

            $cmd.Parameters.Add($param) | Out-Null

        }


        #Open Database and read Data
        try
        {
            if ($cnx.State -eq [System.Data.ConnectionState]::Closed)
            {
                Write-Verbose "Open Connexion"
                $cnx.Open()
            }
            else
            {
                Write-Verbose "Connexion is already open"                
            }

            if ($PSCmdlet.ParameterSetName -eq "TransactionExists")
            {
                Write-Verbose "Transaction exist"
                $cmd.Transaction = $trans            
                
            }

            #$trans = $cnx.BeginTransaction([System.Data.IsolationLevel]::ReadCommitted)
            Write-Verbose "Execute Query"
            $returnValue = $cmd.ExecuteNonQuery()
            Write-Verbose "Execute Query return value : $returnValue"
        }
        catch [Exception]
        {
            Throw
        }
        finally
        {
            #Free resources
            if ($cmd -ne $null)
            {
                $cmd.Dispose()
            }
        
        
            if (($cnx -ne $null) -and ($PSCmdlet.ParameterSetName -ne "ConnectionExists") -and ($PSCmdlet.ParameterSetName -ne "TransactionExists"))
            {
                if ($cnx.State -eq [System.Data.ConnectionState]::Open)
                {
                    $cnx.Close()
                }
                $cnx.Dispose()
            }

        }


    }
}

<#
.SYNOPSIS
Execute a ExecuteReader query

.DESCRIPTION
Use this cmdlet to execute select query on the databse and retrieve a Powershell object in the pipeline.
Name of property is the name of the column query. DbNull become a $null value

.PARAMETER DatabaseConnection
Required. The powershell database connection object (Not DbConnection of the framework.Net). The DBConnection si provided by this Db Factory property of this object
.PARAMETER query
Required. The sql query to execute
#>
function Get-DatabaseData {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
        [PSOBject]
        $DatabaseConnection,

        [Parameter(Mandatory=$true,ValueFromPipeline=$false)]
        [string]$query
    )
    Begin
    {
        $watch = [system.diagnostics.stopwatch]::StartNew() 
        write-verbose "Start Get-DatabaseData from $DatabaseConnection"

    }
    End
    {
        write-verbose "End Get-DatabaseData from $DatabaseConnection"
        $watch.Stop();
        Write-Verbose "Function duration : $($watch.Elapsed)"
    }
    Process
    {
        #Prepare Database Access
        $cnx = $DatabaseConnection.DbFactory.CreateConnection()
        $cnx.ConnectionString = $DatabaseConnection.connectionString
        $cmd = $DatabaseConnection.DbFactory.CreateCommand()
        $cmd.CommandText = $query
        $cmd.Connection = $cnx
        Write-Verbose "Execute query : $query"

        #Open Database and read Data
        try
        {
            $cnx.Open()

            $reader = $cmd.ExecuteReader()
            while($reader.Read())
            {
                $newObj = New-Object PSObject

                for($i=0;$i -lt $reader.FieldCount;$i++)
                {
                    $columnName = $reader.GetName($i)
                    if ([System.String]::IsNullOrEmpty($columnName))
                    {
                        $columnName = "Col$i"
                    }

                    $dbValue = $reader.GetValue($i)
                    if ($dbValue -is [System.DBNull]) {$dbValue = $null}
                    $newObj | Add-Member -MemberType NoteProperty -Name $columnName -Value $dbValue
                }

                Write-Output $newObj
            }
            $reader.Close()
        }
        finally
        {
            #Free resources
            if ($cmd -ne $null)
            {
                $cmd.Dispose()
            }
        
        
            if ($cnx -ne $null)
            {
                if ($cnx.State -eq [System.Data.ConnectionState]::Open)
                {
                    $cnx.Close()
                }
                $cnx.Dispose()
            }

        }
    }
}
